﻿create or replace package IND_TOOLS is

  -- Author  : ALEYTE
  -- Created : 01.06.12
  -- Purpose : Инструменты, помогающие в работе
  function vers return varchar2;
	procedure logging (i_bp_name  in varchar2 default null,
                                i_active_table in varchar2 default null,
                                i_action_name in char default null,
                                i_market_key in char default null,
                                i_city  in varchar2 default null,
                                i_used_date in date default null,
                                i_executed in date default null,
                                i_count_rows in number default null,
                                i_add_info in clob default null,
																i_user_info_v in varchar2 default null,
																i_user_info_n in number default null,
																i_sql_stmt in varchar2 default null);
  function nvl2c
		(expr1 varchar2, -- выражение сравнения
		 expr2 varchar2, -- если выражение не пустое
		 expr3 varchar2) -- если выражение пустое
  return varchar2;
	procedure UpdBlkLst;
	procedure B2B_BlkLstDelete (i_table_name in varchar2, o_count out number);
	function B2B_BlkLstDelete (i_table_name in varchar2) return number;
  procedure CampSubgrp_IDBYCode(i_camp_code  in varchar2, o_camp_id out number, o_subgroup_id out number);
	procedure check_jobs;
	procedure b2b_socs_load;
	procedure b2b_gprs_arpu_load(i_last_month in date);
	procedure b2b_total_arpu_load(i_last_month in date);
end IND_TOOLS;
/
create or replace package body IND_TOOLS is

  g_user  varchar2(100);
  g_schema  varchar2(100);
	g_mail_body_html  clob;

	function vers return varchar2 is
	begin
		return '1.0.0';
	end vers;

  function nvl2c
    (expr1 varchar2, -- выражение сравнения
     expr2 varchar2, -- если выражение не пустое
		 expr3 varchar2) -- если выражение пустое
	return varchar2 is
	begin 
		if expr1 is not null then return(expr2); else return(expr3); end if;
	end nvl2c;

	procedure logging (i_bp_name  in varchar2 default null,
																i_active_table in varchar2 default null,
																i_action_name in char default null,
																i_market_key in char default null,
																i_city  in varchar2 default null,
																i_used_date in date default null,
																i_executed in date default null,
																i_count_rows in number default null,
																i_add_info in clob default null,
																i_user_info_v in varchar2 default null,
																i_user_info_n in number default null,
																i_sql_stmt in varchar2 default null)
		is
		l_sysdate  date;
		l_executed  date;
		l_executed_sec  number;
		l_executed_min  number;
		l_executed_hour  number;
		l_executed_days  number;
		l_bp_name  varchar2(100);
		l_active_table  varchar2(100);
		l_action_name  char(1);
		l_market_key  char(3);
		l_city  varchar2(100);
		l_used_date  date;
		l_count_rows  number;
		l_add_info  clob;
		l_user_info_v  varchar2(100);
		l_user_info_n  number;
		l_sql_stmt  varchar2(32000);
	begin
		l_sysdate := sysdate;
		l_executed := nvl(i_executed, l_sysdate);
		l_executed_sec := (l_sysdate - l_executed) * 24 * 60 * 60;
		l_executed_min := round((l_sysdate - l_executed) * 24 * 60, 1);
		l_executed_hour := round((l_sysdate - l_executed) * 24, 1);
		l_executed_days := round((l_sysdate - l_executed), 1);
		l_bp_name := nvl(upper(i_bp_name), 'NONE');
		l_active_table := nvl(upper(i_active_table), 'NONE');
		l_action_name := nvl(upper(i_action_name), 'N');
		l_market_key := nvl(upper(i_market_key), '-99');
		l_city := nvl(lower(i_city), 'ALL');
		l_used_date := i_used_date;
		l_count_rows := nvl(i_count_rows, 0);
		l_add_info := i_add_info;
		l_user_info_v := i_user_info_v;
		l_user_info_n := i_user_info_n;
		l_sql_stmt := i_sql_stmt;

		insert into
			ind_logs (log_id, time_key, user_name, bp_name, active_table, action_name, market_key, city,
										market_id, used_date, count_rows, executed_sec, executed_min, executed_hour, executed_days,
										add_info, user_info_v, user_info_n, sql_stmt)
		values (
			ind_logs$seq.nextval, l_sysdate, g_user, l_bp_name, l_active_table, l_action_name, l_market_key, l_city,
			null /*market_id*/, l_used_date, l_count_rows, l_executed_sec, l_executed_min, l_executed_hour, l_executed_days,
			l_add_info, l_user_info_v, l_user_info_n, l_sql_stmt);
		commit;
		dbms_output.put_line(nvl2c(l_sysdate,'l_sysdate = '||l_sysdate,'')||
		nvl2c(g_user,', g_user = '||g_user,'')||
		nvl2c(l_bp_name,', l_bp_name = '||l_bp_name,'')||
		nvl2c(l_active_table,', l_active_table = '||l_active_table,'')||
		nvl2c(l_action_name,', l_action_name = '||l_action_name,'')||
		nvl2c(l_market_key,', l_market_key = '||l_market_key,'')||
		nvl2c(l_city,', l_city = '||l_city,'')||
		nvl2c(l_used_date,', l_used_date = '||l_used_date,'')||
		nvl2c(l_count_rows,', l_count_rows = '||l_count_rows,'')||
		nvl2c(l_executed_sec,', l_executed_sec = '||l_executed_sec,'')||
		nvl2c(l_executed_min,', l_executed_min = '||l_executed_min,'')||
		nvl2c(l_executed_hour,', l_executed_hour = '||l_executed_hour,'')||
		nvl2c(l_executed_days,', l_executed_days = '||l_executed_days,'')||
		nvl2c(l_add_info,', l_add_info = '||l_add_info,'')||
		nvl2c(l_user_info_v,', l_user_info_v = '||l_user_info_v,'')||
		nvl2c(l_user_info_n,', l_user_info_n = '||l_user_info_n,'')||
		nvl2c(l_sql_stmt,', l_sql_stmt = '||l_sql_stmt,''));
	end logging;

  procedure b2b_log(
		i_step_name in VARCHAR2,
		i_sql_stmt in VARCHAR2,
		i_time_start in DATE,
		i_time_finish in DATE,
		i_cownt_rows in NUMBER
		) is
	begin
		insert into al_b2b_log (step_name, sql_stmt, time_start, time_finish, cownt_rows)
		values (i_step_name, i_sql_stmt, i_time_start, i_time_finish, i_cownt_rows);
		commit;
	end ;

  procedure UpdBlkLst is
		l_sysdate  date;
		l_count  number;
		l_global_count  number := 0;
		l_table_name  varchar2(100);
	begin
		execute immediate 'select distinct 0 from dwh.fct_agr_application@DWHPRD_REP_TM fss where fss.subs_market_key = ''TUM'' and fss.agreement_type in (''B_NO_PROM'') and ent_expiration_date is null'
		  into l_count;-- проверка на доступность дблинка
		execute immediate 'truncate table ind_fttb_blk_lst_no_promo';
		--execute immediate 'truncate table ind_fttb_blk_lst_bno_promo';
		execute immediate 'truncate table ind_fttb_blk_lst_agreement';
		execute immediate 'truncate table ind_fttb_blk_lst_payer_ban';

		l_sysdate := sysdate;
		l_table_name := 'IND_FTTB_BLK_LST_NO_PROMO';
		execute immediate
		'insert into ind_fttb_blk_lst_no_promo (subs_key, market_key, ban_key, soc_effective_date)
		 select --distinct
			 decode(fss.soc_code, ''NO_PROMO'', fss.subs_key, null),
		 	 fss.market_key,
			 decode(fss.soc_code, ''BNO_PROMO'', fss.ban_key, null),
			 fss.soc_effective_date
		 from dwh.fct_service_subscription@DWHPRD_REP_TM fss
		 where
		 	 fss.soc_effective_date >= to_date(''09.04.2010'',''DD.MM.YYYY'') and
			 fss.soc_code in (''NO_PROMO'', ''BNO_PROMO'') and
		 	 fss.soc_expiration_date > sysdate';
    l_count := sql%rowcount;
		l_global_count := l_count;
    logging(i_bp_name => 'Black List Update', i_active_table => l_table_name, i_action_name => 'I',
		            i_executed => l_sysdate, i_count_rows => l_count);
    g_mail_body_html := 'По NO_PROMO и BNO_PROMO обновилось '||l_count||' строк.'||htf.br;

		/*l_table_name := 'IND_FTTB_BLK_LST_BNO_PROMO';
		execute immediate
		'insert into ind_fttb_blk_lst_bno_promo (ban_key, market_key)
		 select distinct
			 fss.ban_key,
		 	 fss.market_key
		 from dwh.fct_service_subscription@DWHPRD_REP_TM fss
		 where
		 	 fss.soc_effective_date >= to_date(''01.06.2011'',''DD.MM.YYYY'') and
			 fss.soc_code = ''BNO_PROMO'' and
		 	 fss.soc_expiration_date > sysdate';
    l_count := sql%rowcount;
		l_global_count := l_global_count + l_count;
    logging(i_bp_name => 'Black List Update', i_active_table => l_table_name, i_action_name => 'I',
		            i_executed => l_sysdate, i_count_rows => l_count);
    g_mail_body_html := 'По BNO_PROMO обновилось '||l_count||' строк.'||htf.br;*/

		l_sysdate := sysdate;
		l_table_name := 'IND_FTTB_BLK_LST_AGREEMENT';
		execute immediate
		'insert into ind_fttb_blk_lst_agreement (ban_key, market_key, agreement_type)
		 select distinct
		 	 fss.ban_key,
		 	 fss.subs_market_key,
		 	 fss.agreement_type
		 from dwh.fct_agr_application@DWHPRD_REP_TM fss
		 where
		 	 fss.agreement_type in (''B_NO_PROM'')
		   and ent_expiration_date is null';
    l_count := sql%rowcount;
		l_global_count := l_global_count + l_count;
    logging(i_bp_name => 'Black List Update', i_active_table => l_table_name, i_action_name => 'I',
		            i_executed => l_sysdate, i_count_rows => l_count);
    g_mail_body_html := g_mail_body_html||'По AGREEMENT B_NO_PROM обновилось '||l_count||' строк.'||htf.br;

		l_sysdate := sysdate;
		l_table_name := 'IND_FTTB_BLK_LST_PAYER_BAN';
		execute immediate
		'insert into ind_fttb_blk_lst_payer_ban
		 select distinct
			 fss.ban_key,
			 fss.market_key,
			 fss.payer_ban
		 from dwh.dim_ban@DWHPRD_REP_TM fss
		 where
			 fss.payer_ban is not null';
    l_count := sql%rowcount;
		l_global_count := l_global_count + l_count;
    logging(i_bp_name => 'Black List Update', i_active_table => l_table_name, i_action_name => 'I',
		            i_executed => l_sysdate, i_count_rows => l_count);
    g_mail_body_html := g_mail_body_html||'По PAYER_BAN обновилось '||l_count||' строк.'||htf.br;
    g_mail_body_html := 'Black List обновился корректно, всего добавлено '||l_global_count||' строк.'||htf.br||g_mail_body_html;
		ind_mail.mail_history(i_message_html => g_mail_body_html,
													 i_subject => 'Обновление Black List''а',
													 i_job => 4269,
													 i_type_ind => 'D');
  exception
		when others then
			logging(i_bp_name => 'Black List Update', i_active_table => l_table_name, i_action_name => 'I',
									i_executed => l_sysdate, i_user_info_v => 'ERROR', i_add_info => sqlerrm);
		  g_mail_body_html := 'Black List не смог обновиться: "'||sqlerrm||'"';
			ind_mail.mail_history(i_message_html => g_mail_body_html,
													 i_subject => 'Обновление Black List''а',
													 i_job => 4269,
													 i_type_ind => 'D');
	end UpdBlkLst;

	procedure FTTB_BlkLstDelete (i_table_name in varchar2, o_count out number) is
		l_sysdate  date;
		l_count  number;
	begin
		l_sysdate := sysdate; o_count := 0;
		execute immediate 'delete '||i_table_name||' where subs_key in (select subs_key from rep_tm.ind_fttb_blk_lst_no_promo)';
		l_count := sql%rowcount;
		o_count := o_count + l_count;
		execute immediate 'delete '||i_table_name||' where ban_key in (select ban_key from rep_tm.ind_fttb_blk_lst_agreement)';
		l_count := sql%rowcount;
		o_count := o_count + l_count;
		--смотрим, что на бане есть услуги, смотрим есть ли этот бан в списке пейер банов.
		execute immediate 'delete '||i_table_name||' t where ban_key in (select p.ban_key from rep_tm.ind_fttb_blk_lst_payer_ban p, rep_tm.ind_fttb_blk_lst_agreement n where p.payer_ban = n.ban_key)';
		l_count := sql%rowcount;
		o_count := o_count + l_count;
		execute immediate 'delete '||i_table_name||' where subs_key in (select subs_key from ind_fttb_subs_stop_list@REP_AN)';
		l_count := sql%rowcount;
		o_count := o_count + l_count;
		execute immediate 'delete '||i_table_name||' where subs_key in (select phones from ind_moz_stop_list_fttb where phones is not null)';
		l_count := sql%rowcount;
		o_count := o_count + l_count;
		execute immediate 'delete '||i_table_name||' where subs_key in (select phones from IND_MOZ_STOP_LIST_FTTB)';
		l_count := sql%rowcount;
		o_count := o_count + l_count;
		execute immediate 'delete '||i_table_name||' where subs_key in (select subs_key from ind_crm_id_del_arh@rep_an b where b.response_class=538)';
		l_count := sql%rowcount;
		o_count := o_count + l_count;
		logging(i_active_table => upper(i_table_name), i_action_name => 'D', i_executed => l_sysdate, i_count_rows => o_count);
  exception
		when others then
			logging(i_bp_name => 'FTTB Black List Delete', i_active_table => i_table_name, i_action_name => 'D',
									i_executed => l_sysdate, i_user_info_v => 'ERROR', i_add_info => sqlerrm); o_count := 0;
	end FTTB_BlkLstDelete;

	procedure B2B_BlkLstDelete (i_table_name in varchar2, o_count out number) is
		l_sysdate  date;
	begin
		l_sysdate := sysdate;
    FTTB_BlkLstDelete (i_table_name, o_count);
  exception
		when others then
			logging(i_bp_name => 'FTTB Black List Delete', i_active_table => i_table_name, i_action_name => 'D',
									i_executed => l_sysdate, i_user_info_v => 'ERROR', i_add_info => sqlerrm); o_count := 0;
	end B2B_BlkLstDelete;

	function B2B_BlkLstDelete (i_table_name in varchar2) return number is
		l_sysdate  date;
		l_count  number;
		l_count_begin  number;
		l_count_end  number;
		o_count  number;
		l_message  clob;
	begin
		l_sysdate := sysdate; o_count := 0;
		execute immediate 'select count(*) from '||i_table_name into l_count_begin;
		l_message := '-- Было записей: '||l_count_begin||utl_tcp.crlf;
		execute immediate 'delete '||i_table_name||' where subs_key in (select subs_key from rep_tm.ind_fttb_blk_lst_no_promo where subs_key is not null)';
		l_count := sql%rowcount;
		o_count := o_count + l_count;
		l_message := l_message||'-- Удалено CTN с NO_PROMO: '||l_count||utl_tcp.crlf;
		execute immediate 'delete '||i_table_name||' where ban_key in (select distinct ban_key from rep_tm.ind_fttb_blk_lst_no_promo where ban_key is not null)';
		l_count := sql%rowcount;
		o_count := o_count + l_count;
		l_message := l_message||'-- Удалено CTN c BAN с SOC BNO_PROMO: '||l_count||utl_tcp.crlf;
		execute immediate 'delete '||i_table_name||' where ban_key in (select ban_key from rep_tm.ind_fttb_blk_lst_agreement)';
		l_count := sql%rowcount;
		o_count := o_count + l_count;
		l_message := l_message||'-- Удалено CTN c BAN с agreement B_NO_PROMO: '||l_count||utl_tcp.crlf;
		--смотрим, что на бане есть услуги, смотрим есть ли этот бан в списке пейер банов.
		execute immediate 'delete '||i_table_name||' t where ban_key in (select p.ban_key from rep_tm.ind_fttb_blk_lst_payer_ban p, rep_tm.ind_fttb_blk_lst_agreement n where p.payer_ban = n.ban_key)';
		l_count := sql%rowcount;
		o_count := o_count + l_count;
		l_message := l_message||'-- Удалено CTN c BAN с PAYER_BAN c B_NO_PROMO: '||l_count;
		execute immediate 'select count(*) from '||i_table_name into l_count_end;
		logging(i_active_table => upper(i_table_name), i_action_name => 'D', i_executed => l_sysdate, i_count_rows => o_count);
		l_message := l_message||'.'||utl_tcp.crlf||'-- Всего удалено: '||o_count||' записей, осталось: '||l_count_end||' записей.';
		dbms_output.put_line(l_message);
		return o_count;
  exception
		when others then
			dbms_output.put_line('ERROR: '||sqlerrm);
			logging(i_bp_name => 'Black List Delete', i_active_table => i_table_name, i_action_name => 'D',
									i_executed => l_sysdate, i_user_info_v => 'ERROR', i_add_info => sqlerrm); return -1;
	end B2B_BlkLstDelete;

  procedure CampSubgrp_IDBYCode(i_camp_code  in varchar2, o_camp_id out number, o_subgroup_id out number) is
  begin
	 	select 
		distinct a.subgroup_id, d.camp_id
		into o_subgroup_id, o_camp_id
		from
					cms.camp_subgroups@REP_AN a,
					cms.camp_library@REP_AN d 
		where a.camp_id = d.camp_id
			and d.camp_code = i_camp_code
			and d.camp_type_id = 45
			order by a.subgroup_id;
  end CampSubgrp_IDBYCode;

  procedure check_jobs as
		type t_broken_jobs_rec is record (
		  job user_jobs.JOB%TYPE,
			last_date user_jobs.LAST_DATE%TYPE,
			last_time user_jobs.LAST_SEC%TYPE,
			what user_jobs.WHAT%TYPE,
			failures user_jobs.FAILURES%TYPE,
			total_time_hour user_jobs.TOTAL_TIME%TYPE);
	  type t_broken_jobs is table of t_broken_jobs_rec index by binary_integer;
		l_broken_jobs  t_broken_jobs;
	begin
		select job, last_date, last_sec, what, FAILURES, trunc(TOTAL_TIME/60/60,2) TOTAL_TIME_HOUR
		bulk collect into l_broken_jobs 
		from user_jobs
    where BROKEN = 'Y'
      and job != 4286;

		if l_broken_jobs.count > 0 then
			g_mail_body_html := 'Появились сломанные джобы' || htf.br || htf.br ||
			  htf.tableOpen(cborder => 'border="1"', cnowrap => 'NOWRAP') || htf.tableRowOpen(calign => 'center', cvalign => 'center') ||
			  htf.tableData(cvalue => 'Job #', cattributes => 'WIDTH=50') || htf.tableData(cvalue => 'Last Date', cattributes => 'WIDTH=80') ||
				htf.tableData(cvalue => 'What', cattributes => 'WIDTH=250') || htf.tableData(cvalue => 'Failures', cattributes => 'WIDTH=55') ||
				htf.tableData(cvalue => 'Total time (hour)', cattributes => 'WIDTH=50') || htf.tableRowClose;
			for i in l_broken_jobs.first..l_broken_jobs.last loop
				g_mail_body_html := g_mail_body_html || htf.tableRowOpen ||
				  htf.tableData(cvalue => l_broken_jobs(i).job) || htf.tableData(cvalue => l_broken_jobs(i).last_date || ' ' || l_broken_jobs(i).last_time) ||
					htf.tableData(cvalue => l_broken_jobs(i).what) || htf.tableData(cvalue => l_broken_jobs(i).failures) ||
					htf.tableData(cvalue => l_broken_jobs(i).total_time_hour) ||
				  htf.tableRowClose;
			end loop;
			g_mail_body_html := g_mail_body_html || htf.tableClose;
			ind_mail.send_mail(
			  i_recipient => 'ALeyte@beeline.ru',
				i_message => g_mail_body_html,
				i_subject => 'Сломанные джобы',
				i_message_type => 'H'
			);
		else
			null;
		end if;
	end check_jobs;

  procedure b2b_socs_load/*(i_task_num in varchar2 default null)*/ is
	begin
		execute immediate 'truncate table AL_B2B_ALL_SOCS';
		execute immediate 'truncate table AL_B2B_ALL_NO_PP1';
		for cur_market in (select market_key, super_region_name from al_b2b_all_markets) loop
			--Не подключен SOC безлимитного интернета
			insert /*+ append*/ into AL_B2B_ALL_SOCS(subs_key, ban_key, market_key, super_region_name, soc_code, soc_effective_date, soc_expiration_date)
			select fss.subs_key,
						 fss.ban_key,
						 fss.market_key,
						 cur_market.super_region_name,
						 fss.soc_code,
						 fss.soc_effective_date,
						 fss.soc_expiration_date
			from dwh.fct_service_subscription@dwhprd_rep_tm fss
			where
				fss.market_key = cur_market.market_key and
				fss.soc_code in (
					'GPRS_LMSK','GPRS_VIPL' --VIP
		     ,'CR_UI1L' --CNT
		     ,'03GUN1VL' -- VLG
		     ,'UNI240_UR','UNI300_UR' -- Ural
		     ,'06UISBP' -- SIB
		     ,'UNGP3GB_1','UNGP3GB_2' -- NW
		      --,'tbd' --UG
		     ,'04UNL_1V', '04UNL_1'--STH
		     ,'BGKGPLIM','27GPLIM','79GPLIM','KSKGPLIM','04GPLIM','IRKGPLIM','41GPLIM','49GPLIM','VLAGPLIM','75GPLIM',
				  '87GPLIM','SKHGPLIM','14GPLIM',
		      'BGKGPLIM2','27GPLIM2','79GPLIM2','KSKGPLIM2','04GPLIM2','IRKGPLIM2','41GPLIM2','49GPLIM2','VLAGPLIM2',
					'75GPLIM2','87GPLIM2','SKHGPLIM2','14GPLIM2'-- FE
				)
				and fss.soc_expiration_date > sysdate
				and fss.soc_effective_date >= to_date('01082011','ddmmyyyy')
			;--16:00 
			commit;

			--  11) Не подключены ТП (
			--L:\Targeted_Marketing\В2В\доступность-исключения\БИ\Доступность SOCов тарифам.xlsx и
			--L:\Targeted_Marketing\В2В\доступность-исключения\БИ\искл blackberry.docx и
			--L:\Targeted_Marketing\В2В\доступность-исключения\БИ\Доступность прайс-планов (код - SOC).xlsx
			--)
			--1) исключаем указанные ТП на закладке "Исключения"
			--2) исключаем все ТП указанные в документе
			--3) для ТП, которые указаны в файле предложение ТП по безлимитному интернету возможно. Т.е надо исключить ТП, которых нет в этом списке.
			insert /*+ append*/ into al_b2b_all_no_pp1(subs_key, ban_key, market_key, super_region_name, soc_code, soc_effective_date, soc_expiration_date)
			select fss.subs_key,
						 fss.ban_key,
						 fss.market_key,
						 cur_market.super_region_name,
						 fss.soc_code,
						 fss.soc_effective_date,
						 fss.soc_expiration_date
			from dwh.fct_service_subscription@dwhprd_rep_tm fss
			where
				fss.market_key = cur_market.market_key and
				fss.soc_code in ('BB_UNL_09','BBBES_U','BB_ROAM','BB_ROAMN','BLACKB_T','BLACKB_NP','BB_UN_09','BISAI_PO',--blackberry
				'GPRS_LMSK','CR_UI1L','03GUN1VL','UNI240_UR','UNI300_UR','06UISBP','UNGP3GB_1','UNGP3GB_2','04UNL_1','04UNL_1V','BGKGPLIM','27GPLIM',
				'79GPLIM','KSKGPLIM','04GPLIM','IRKGPLIM','41GPLIM','49GPLIM','VLAGPLIM','75GPLIM','87GPLIM','SKHGPLIM','14GPLIM','STT650M','STT850M',
				'AR_IB3000')
				and fss.soc_expiration_date > sysdate
				and fss.soc_effective_date >= to_date('25112009','ddmmyyyy')
			;--35:10
			commit;
		end loop;
	end b2b_socs_load;

  procedure b2b_gprs_arpu_load(i_last_month in date) is
		l_count  number;
		l_start  date;
		l_finish  date;
		l_time_key_1  date;
		l_time_key_2  date;
		l_time_key_3  date;
	begin
		--create table al_b2b_log
		/*Аналог P 69 для prepaid

		Сумма списаний по CHARGE_AMT_RUR + ADDITIONAL_CHARGE_AMT_RUR
		 из DWH.AGG_SUBS_USAGE_MONTHLY_V3,
		где по BUSINESS_SERVICE_KEY в таблице DWH.DIM_BUSINESS_SERVICE_TYPE REVENUE_STREAM = 4 ИЛИ business_service_key in (120104, 120105)
		Временные условия на выборку: TIME_KEY = первый день месяца
		+
		Сумма списаний по (ACTIVITY_AMT + TAX_AMT) * decode(CURRENCY_KEY,'P', 1,'D', nvl(CONVERSION_RATE, 0)
		 из DWH.FCT_CHARGE с условиями на выборку:
		  REVENUE_STREAM = 4
		  И FEATURE_REVENUE_CODE = ‘R’ or (FEATURE_REVENUE_CODE = ‘O’ and ADMIN_IND = ‘1’).
		ADMIN_IND получаем из справочника DWH.DIM_FEATURE, соединяясь с ним по FEATURE_KEY
		Временные условия на выборку:
		  CREATION_DATE_KEY ≥ первый день месяца
		и CREATION_DATE_KEY < первый день следующего месяца.*/

    l_time_key_1 := i_last_month;
		l_time_key_2 := add_months(i_last_month, -1);
		l_time_key_3 := add_months(i_last_month, -2);

		l_start := sysdate;
		execute immediate 'truncate table al_b2b_business_service_key';
		insert into al_b2b_business_service_key(business_service_key, business_service_type_key, business_service_desc_eng, business_service_desc_rus, business_service_type_key_2006, active_ind, vas_ind, business_service_type_key_2007, business_service_key_2007, identity_id, cis_service_ind, population_time, update_time, rfc)
		select t.* from dwh.DIM_BUSINESS_SERVICE@dwhprd_rep_tm t
		where
			business_service_key in (120104, 120105) or
			business_service_type_key in (select business_service_type_key from dwh.dim_business_service_type@dwhprd_rep_tm where revenue_stream=4)
		;
		select count(*) into l_count from al_b2b_business_service_key;
		l_finish := sysdate;
		b2b_log('B2B_GPRS_ARPU_LOAD', 'insert into al_b2b_business_service_key', l_start, l_finish, l_count);

		l_start := sysdate;
		execute immediate 'truncate table al_b2b_admin_ind';
		insert into al_b2b_admin_ind(FEATURE_KEY)
		select FEATURE_KEY
		from DWH.DIM_FEATURE@dwhprd_rep_tm
		where ADMIN_IND = '1';
		select count(*) into l_count from al_b2b_admin_ind;
		l_finish := sysdate;
		b2b_log('B2B_GPRS_ARPU_LOAD', 'insert into al_b2b_admin_ind', l_start, l_finish, l_count);

		l_start := sysdate;
		execute immediate 'truncate table al_b2b_all_gprs_arpu';
		for cur_market in (select market_key, super_region_name from al_b2b_all_markets) loop
			insert /*+ parallel(a, 12)*/ 
			into al_b2b_all_gprs_arpu(ban_key, subs_key, subs_market_key, gprs_arpu1, gprs_arpu2, gprs_arpu3)
			select a.ban_key, a.subs_key, a.subs_market_key,
			sum(decode(a.time_key, l_time_key_1, (charge_amt_rur + additional_charge_amt_rur), 0)) gprs_arpu1,
			sum(decode(a.time_key, l_time_key_2, (charge_amt_rur + additional_charge_amt_rur), 0)) gprs_arpu2,
			sum(decode(a.time_key, l_time_key_3, (charge_amt_rur + additional_charge_amt_rur), 0)) gprs_arpu3
			from
				dwh.agg_subs_usage_monthly_v3@DWHPRD_TM a
			where 
				a.roaming_ind = 0 and --NO ROAMING
				a.business_service_key in (select business_service_key from al_b2b_business_service_key)  and
				a.subs_market_key = cur_market.market_key and
				(a.time_key = l_time_key_1 or a.time_key = l_time_key_2 or a.time_key = l_time_key_3)
			group by a.ban_key, a.subs_key, a.subs_market_key;
			commit;
		end loop;
		select count(*) into l_count from al_b2b_all_gprs_arpu;
		l_finish := sysdate;
		b2b_log('B2B_GPRS_ARPU_LOAD', 'insert into al_b2b_all_gprs_arpu (dwh.agg_subs_usage_monthly_v3@DWHPRD_TM)', l_start, l_finish, l_count);

		l_start := sysdate;
		l_count := 0;
		for cur_market in (select market_key, super_region_name from al_b2b_all_markets) loop
			insert /*+ parallel(fc, 12)*/ 
			into al_b2b_all_gprs_arpu (subs_key, ban_key, subs_market_key, gprs_arpu1, gprs_arpu2, gprs_arpu3)
			select fc.subs_key, fc.ban_key, fc.market_key,
			sum(decode(trunc(CREATION_DATE_KEY,'MM'), l_time_key_1, (ACTIVITY_AMT + TAX_AMT) * decode(CURRENCY_KEY,'P', 1,'D', nvl(CONVERSION_RATE, 0)), 0)),
			sum(decode(trunc(CREATION_DATE_KEY,'MM'), l_time_key_2, (ACTIVITY_AMT + TAX_AMT) * decode(CURRENCY_KEY,'P', 1,'D', nvl(CONVERSION_RATE, 0)), 0)),
			sum(decode(trunc(CREATION_DATE_KEY,'MM'), l_time_key_3, (ACTIVITY_AMT + TAX_AMT) * decode(CURRENCY_KEY,'P', 1,'D', nvl(CONVERSION_RATE, 0)), 0))
			from
				dwh.fct_charge@dwhprd_tm fc
			where
				fc.market_key = cur_market.market_key and
				business_service_key in (select business_service_key from al_b2b_business_service_key where  business_service_key not in (120104, 120105)) and--REVENUE_STREAM = 4 
				(FEATURE_REVENUE_CODE = 'R' or (FEATURE_REVENUE_CODE = 'O' and FEATURE_KEY in (select FEATURE_KEY from al_b2b_admin_ind))) and --ADMIN_IND получаем из справочника DWH.DIM_FEATURE, соединяясь с ним по FEATURE_KEY
				CREATION_DATE_KEY >= l_time_key_3 and--первый день месяца
				CREATION_DATE_KEY < add_months(l_time_key_1, 1)--первый день следующего месяца.
			group by fc.subs_key, fc.ban_key, fc.market_key;
			l_count := l_count + sql%rowcount;
			commit;
    end loop;
		l_finish := sysdate;
		b2b_log('B2B_GPRS_ARPU_LOAD', 'insert into al_b2b_all_gprs_arpu (dwh.fct_charge@dwhprd_tm)', l_start, l_finish, l_count);

		l_start := sysdate;
		execute immediate 'truncate table al_b2b_all_gprs_arpu_agg';
    insert into al_b2b_all_gprs_arpu_agg(subs_key, ban_key, subs_market_key, gprs_arpu1, gprs_arpu2, gprs_arpu3)
		select subs_key, ban_key, subs_market_key, sum(gprs_arpu1) gprs_arpu1, sum(gprs_arpu2) gprs_arpu2, sum(gprs_arpu3) gprs_arpu3
		from al_b2b_all_gprs_arpu
		group by subs_key, ban_key, subs_market_key;
		select count(*) into l_count from al_b2b_all_gprs_arpu_agg;
		l_finish := sysdate;
		b2b_log('B2B_GPRS_ARPU_LOAD', 'insert into al_b2b_all_gprs_arpu_agg', l_start, l_finish, l_count);
	end b2b_gprs_arpu_load;

  procedure b2b_total_arpu_load(i_last_month in date) is
		l_count  number;
		l_start  date;
		l_finish  date;
		l_time_key_1  date;
		l_time_key_2  date;
		l_time_key_3  date;
	begin
    l_time_key_1 := i_last_month;
		l_time_key_2 := add_months(i_last_month, -1);
		l_time_key_3 := add_months(i_last_month, -2);

		l_start := sysdate;
		execute immediate 'truncate table al_b2b_all_subs_mou';
    insert into al_b2b_all_subs_mou(subs_key, ban_key, market_key, voice_arpu, total_arpu1, total_arpu2, total_arpu3)
		select /*+ use_hash(s a) */
			a.subs_key, a.ban_key, a.market_key,
			sum(decode(a.TIME_KEY, l_time_key_1, (a.TOTAL_LOC_DUR + a.TOTAL_LD_DUR + a.TOTAL_INT_DUR)/60, 0))   voice_arpu,
			sum(decode(a.TIME_KEY, l_time_key_1,
			  (a.TOTAL_RC_CHARGES_AMT_R+
				a.TOTAL_ADM_CHARGES_AMT_R+
				a.TOTAL_DISCOUNT_AMT_R+
				a.TOTAL_LOC_CHARGE_AMT_R+
				a.TOTAL_LD_CHARGE_AMT_R+
				a.TOTAL_INT_CHARGE_AMT_R+
				a.TOTAL_OUT_SMS_CHARGE_AMT_R+
				a.TOTAL_ADD_SRV_CHARGE_AMT_R), 0)
				) total_arpu1,
			sum(decode(a.TIME_KEY, l_time_key_2,
			  (a.TOTAL_RC_CHARGES_AMT_R+
				a.TOTAL_ADM_CHARGES_AMT_R+
				a.TOTAL_DISCOUNT_AMT_R+
				a.TOTAL_LOC_CHARGE_AMT_R+
				a.TOTAL_LD_CHARGE_AMT_R+
				a.TOTAL_INT_CHARGE_AMT_R+
				a.TOTAL_OUT_SMS_CHARGE_AMT_R+
				a.TOTAL_ADD_SRV_CHARGE_AMT_R), 0)
				) total_arpu2,
			sum(decode(a.TIME_KEY, l_time_key_3,
			  (a.TOTAL_RC_CHARGES_AMT_R+
				a.TOTAL_ADM_CHARGES_AMT_R+
				a.TOTAL_DISCOUNT_AMT_R+
				a.TOTAL_LOC_CHARGE_AMT_R+
				a.TOTAL_LD_CHARGE_AMT_R+
				a.TOTAL_INT_CHARGE_AMT_R+
				a.TOTAL_OUT_SMS_CHARGE_AMT_R+
				a.TOTAL_ADD_SRV_CHARGE_AMT_R), 0)
				) total_arpu3
		from
			dwh.AGG_ARPU_MOU@dwhprd_rep_tm a
		where
			a.TIME_KEY in (l_time_key_1, l_time_key_2, l_time_key_3)
		group by a.subs_key, a.ban_key, a.market_key;
		select count(*) into l_count from al_b2b_all_subs_mou;
		l_finish := sysdate;
		b2b_log('B2B_TOTAL_ARPU_LOAD', 'insert into al_b2b_all_subs_mou', l_start, l_finish, l_count);
	end b2b_total_arpu_load;

begin
  g_user := sys_context('USERENV', 'OS_USER');
	g_schema := sys_context('USERENV', 'CURRENT_SCHEMA');
exception
	when others then
		logging(i_bp_name => 'package IND_TOOLS', i_user_info_v => 'ERROR', i_add_info => sqlerrm);
end IND_TOOLS;
/
